<?php

define('SIMILAR_MIN', 0.8);

/*
 * 查询对象
 */
function exdb_db() {

	$CI = &get_instance();

	return $CI -> db;

}

/*
 * 安全名称
 */
function exdb_name($name) {

	return '`' . $name . '`';

}

function exdb_dbtable($db, $table) {

	return exdb_name($db) . '.' . exdb_name($table);
}

/*
 * 安全查询
 */
function exdb_debug($debug = 'debug') {

	static $db_debug = 'debug';

	if ($debug == 'debug') {

		if ($debug != 'debug') {

			exdb_db() -> db_debug = $db_debug;
		}

	} else {

		if ($db_debug = 'debug') {

			$db_debug =         exdb_db() -> db_debug;
		}

		exdb_db() -> db_debug = $debug;
	}
}

/*
 * 跨库列出表
 */
function exdb_list_tables($db, $table = false) {

	$sql = 'SHOW TABLES FROM ' . exdb_name($db);

	if ($table != FALSE) {

		$sql .= " LIKE '" . $table . "'";
	}

	$query = exdb_db() -> query($sql);

	$tables = array();

	if ($query != FALSE) {

		while ($row = $query -> _fetch_assoc()) {

			$tables[] = reset($row);
		}
	}

	return $tables;
}

/*
 * 跨库判断表存在
 */
function exdb_table_exists($db, $table) {

	$tables = exdb_list_tables($db, $table);

	return in_array($table, $tables);
}

/*
 * 跨库列出表字段
 */
function exdb_list_fields($db_table, $table = FALSE) {

	if ($table) {

		$db_table = exdb_dbtable($db_table, $table);
	}

	$query = exdb_db() -> query('DESCRIBE ' . $db_table);

	$fields = FALSE;

	if ($query != FALSE) {

		$fields = $query -> result_array();
	}

	return $fields;
}

/*
 * 获取主键字段
 */
function exdb_key_arrays($fields) {

	$rows = FALSE;

	foreach ($fields as $row) {

		if ($row['Key'] == 'PRI') {

			$rows[] = $row;
		}
	}

	return $rows;
}

function exdb_key_fields($fields) {

	$rows = FALSE;

	foreach ($fields as $row) {

		if ($row['Key'] == 'PRI') {

			$rows[] = $row['Field'];
		}
	}

	return $rows;
}

/*
 * 获取文字字段
 */
function exdb_char_arrays($fields) {

	$rows = FALSE;

	foreach ($fields as $row) {

		if (preg_match("/(text|char)/i", $row['Type'])) {

			$rows[] = $row;
		}
	}

	return $rows;
}

function exdb_char_fields($fields) {

	$rows = FALSE;

	foreach ($fields as $row) {

		if (preg_match("/(text|char)/i", $row['Type'])) {

			$rows[] = $row['Field'];
		}
	}

	return $rows;
}

/*
 * 字段数量百分比
 */
function exdb_similar_percent(&$field_rows, $field_dbtable) {

	$sql = "SELECT * FROM " . $field_dbtable;

	$count = exdb_db() -> query($sql) -> num_rows();

	foreach ($field_rows as &$field_row) {

		$field_count = exdb_db() -> query($sql . ' GROUP BY ' . exdb_name($field_row['Field'])) -> num_rows();

		$field_row['Percent'] = ($field_count + 1.0) / ($count + 1.0);
	}
}

/*
 * 字段匹配度
 */
function exdb_similar_field($from_row, $to_row) {

	similar_text($from_row['Type'], $to_row['Type'], $sim_t) / 100.0;
	similar_text($from_row['Field'], $to_row['Field'], $sim_f) / 100.0;

	$sim_t /= 100.0;
	$sim_f /= 100.0;

	if (array_key_exists('Percent', $from_row) && ($from_percent = $from_row['Percent']) && ($to_percent = $to_row['Percent'])) {

		$sim_p = 1.0 - abs($from_percent - $to_percent);

		$sim_t = $sim_t * 0.56 + $sim_p * 0.6;
		$sim_f = $sim_f * 0.5 + $sim_p * 0.6;
	}

	$sim = $sim_t * 0.68 + $sim_f * 0.32;

	return $sim;
}

/*
 * 枚举字段匹配
 */
function exdb_similar_fields(&$from_rows, &$to_rows, $from_dbtable = FALSE, $to_dbtable = FALSE, $SIMILAR_MIN = SIMILAR_MIN) {

	$from_fields = array();
	$to_fields = array();

	$from_others = array();
	$to_others = array();

	$status = array('from_fields' => &$from_fields, 'to_fields' => &$to_fields, 'from_others' => &$from_others, 'to_others' => &$to_others);

	/*
	 * 匹配字段数量
	 */
	$field_percent = FALSE;
	if ($from_dbtable && $to_dbtable) {

		$field_percent = TRUE;

		exdb_similar_percent($from_rows, $from_dbtable);
		exdb_similar_percent($to_rows, $to_dbtable);
	}

	/*
	 * 枚举匹配
	 */
	while (($from_count = count($from_rows)) > 0 && ($to_count = count($to_rows)) > 0) {

		if ($from_count == $to_count && 1 == $to_count && $field_percent) {

			$from_row = reset($from_rows);
			$to_row = reset($to_rows);

			similar_text($from_row['Type'], $to_row['Type'], $similar);
			$similar /= 100.0;

			if ($similar > $SIMILAR_MIN) {

				$from_fields[] = $from_row['Field'];
				$to_fields[] = $to_row['Field'];

				$from_rows = $to_rows = array();
			}

			break;

		} else {

			$similar = $SIMILAR_MIN;

			$from_key = $to_key = '';

			foreach ($from_rows as $f_key => $from_row) {

				foreach ($to_rows as $t_key => $to_row) {

					$sim = exdb_similar_field($from_row, $to_row);

					if ($sim > $similar) {

						$similar = $sim;

						$from_key = $f_key;
						$to_key = $t_key;
					}
				}
			}

			/*
			 * 未达到最低匹配度
			 */
			if ($similar <= $SIMILAR_MIN)
				break;

			/*
			 * 保存匹配字段
			 */
			$from_fields[] = $from_rows[$from_key]['Field'];
			$to_fields[] = $to_rows[$to_key]['Field'];

			/*
			 * 删除已经匹配字段
			 */
			unset($from_rows[$from_key]);
			unset($to_rows[$to_key]);
		}
	}

	/*
	 * 未匹配字段
	 */
	foreach ($from_rows as $from_row) {

		$from_others[] = $from_row['Field'];
	}
	foreach ($to_rows as $to_row) {

		$to_others[] = $to_row['Field'];
	}

	return $status;
}

/*
 * 复合索引检测唯一
 */
function exdb_fields_iskey($fields, $dbtable) {

	if (count($fields) <= 0)
		return FALSE;

	$sql = 'SELECT * FROM ' . $dbtable . ' AS a ';
	$sql .= ' LEFT JOIN ' . $dbtable . ' AS b on ';

	$condition = ' WHERE ';
	$next = FALSE;
	foreach ($fields as $field) {

		if ($next) {

			$sql .= ' AND ';
			$condition .= ' AND ';

		} else {

			$next = TRUE;
		}

		$field = exdb_name($field);

		$sql .= ' a.' . $field . ' = b.' . $field;
		$condition .= ' a.' . $field . ' != b.' . $field;
	}

	$sql .= $condition;

	return exdb_db() -> query($sql) -> num_rows() <= 0;
}

/*
 * 文本|复合索引联合查询
 */
function exdb_keys_chars_query($from_dbtable, $to_dbtable, $from_keys, $to_keys, $from_chars, $to_chars, $limit = FALSE) {

	$from_sql = '';
	$on_sql = '';

	$next = FALSE;
	foreach ($from_keys as $key => $from_key) {
		$to_key = $to_keys[$key];

		$from_key = exdb_name($from_key);
		$to_key = exdb_name($to_key);

		if ($next) {

			$from_sql .= ' , ';
			$on_sql .= ' AND ';

		} else {

			$from_sql .= ' b.' . $from_key . ' as tid , ';
			$next = TRUE;
		}

		$from_sql .= ' a.' . $from_key . ' as key_' . $key;
		$on_sql .= ' a.' . $from_key . ' = ' . ' b.' . $to_key;
	}

	foreach ($from_chars as $key => $from_char) {
		$to_char = $to_chars[$key];

		$from_char = exdb_name($from_char);
		$to_char = exdb_name($to_char);

		$from_sql .= ' , a.' . $from_char . ' as from_' . $key;
		$from_sql .= ' , b.' . $to_char . ' as to_' . $key;
	}

	$sql = 'SELECT ' . $from_sql . ' FROM ' . $from_dbtable . ' AS a ';
	$sql .= ' LEFT JOIN ' . $to_dbtable . ' AS b ON ' . $on_sql;

	if ($limit) {

		$sql .= ' limit ' . $limit;
	}

	//echo $sql;
	return exdb_db() -> query($sql);
}
